package com.javaex.book.dao.impl;

import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.javaex.book.utils.BeanUtils;
import com.javaex.book.utils.JdbcUtils;
import com.javaex.book.utils.RegexUtils; 

public class BaseDao {
	
	private static final Logger logger = LoggerFactory.getLogger(BaseDao.class);

	private static final String KEY_WORD_INSERT = "insert";
	private static final int PARAMETER_COUNT = 1;
	private static final String METHOD_PREFIX = "get";
	
	/**
	 * ResultSet中，方法返回值与方法的映射关系。用于根据返回值获取ResultSet中指定方法
	 * 
	 * <p>提前确定好映射关系，不用每次使用反射获取，优化性能
	 * 
	 * <li> key为方法返回值类型
	 * <li> value为方法实例
	 */
	private static final Map<Class<?>, Method> methodMapping = new HashMap<>();
	
	static {
		// 确定ResultSet中方法返回值与方法实例的映射关系
		determineMethodMappingForResultSet();
	}
	
	/**
 	 * 执行增、删、改的SQL语句
	 * @param sql SQL语句
	 * @param params 参数
	 * @return 0表示执行失败，大于0成功
	 * @throws SQLException 
	 */
	protected Integer execute(String sql, Object[] params) {
		Integer result = 0;
		Connection connection = JdbcUtils.getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			// 为预编译sql设置参数
			if (params != null) {
                for (int i = 0; i < params.length; i++) {
                	statement.setObject(i + 1, params[i]); 
                }
            }
			logger.info("SQL：{}", statement.toString().split(": ")[1]);
			result = statement.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close(statement, null);
		}
		return result;
	}
	
	/**
	 * 执行增、删、改的SQL语句
	 * @param sql SQL语句
	 * @param obj Bean类型的参数
	 * @return 0表示执行失败，大于0成功
	 * @throws SQLException 
	 */
	protected int execute(String sql, Object obj) {
		return execute(sql, convertBeanToParams(sql, obj));
	}
	
	/**
	 * 统计指定表的数据总量
	 * @param tableName 表名
	 * @return 数据总量
	 * @throws SQLException 
	 */
	protected Integer count(String sql, Object[] params) {
		Connection connection = JdbcUtils.getConnection();
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			statement = connection.prepareStatement(sql);
			// 为预编译sql设置参数
			if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                	statement.setObject(i + 1, params[i]); 
                }
            }
			logger.info("SQL：{}", statement.toString().split(": ")[1]);
			rs = statement.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close(statement, rs);
		}
		return 0;
	}
	
	/**
	 * 执行查询SQL，返回一个JavaBean对象
	 * @param <T> JavaBean类型
	 * @param sql 查询SQL语句
	 * @param clazz JavaBean类型的Class对象
	 * @param params 查询参数
	 * @return JavaBean对象
	 * @throws SQLException 
	 */
	protected <T> T findOne(String sql, Class<T> clazz, Object[] params) {
		Connection connection = JdbcUtils.getConnection();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.prepareStatement(sql);
			// 为预编译sql设置参数
			if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                	statement.setObject(i + 1, params[i]); 
                }
            }
			logger.info("SQL：{}", statement.toString().split(": ")[1]);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				// 将查询结果转为JavaBean实例
				ResultSet resTemp = resultSet;
				return BeanUtils.injectDataIntoBean(clazz, (type, name) -> {
					Method method = methodMapping.get(type);
					if (method == null) {
						return null;
					}
					try {
						return method.invoke(resTemp, name);
					} catch (Exception e) {
						logger.error("从ResultSet取值失败，name = {}", name, e);
					}
					return null; 
				});
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close(statement, resultSet);
		}
		return null;
	}
	
	/**
	 * 执行查询SQL，返回JavaBean集合
	 * @param <T> JavaBean类型
	 * @param sql 查询SQL语句
	 * @param clazz JavaBean类型的Class对象
	 * @param params 查询参数
	 * @return JavaBean集合
	 * @throws SQLException 
	 */
	protected <T> List<T> findMany(String sql, Class<T> clazz, Object[] params) {
		List<T> list = new ArrayList<T>();
		Connection connection = JdbcUtils.getConnection();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.prepareStatement(sql);
			// 为预编译sql设置参数
			if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                	statement.setObject(i + 1, params[i]); 
                }
            }
			logger.info("SQL：{}", statement.toString().split(": ")[1]);
			resultSet = statement.executeQuery();
			// 将查询结果转为JavaBean实例
			while (resultSet.next()) {
				ResultSet resTemp = resultSet;
				T bean = BeanUtils.injectDataIntoBean(clazz, (type, name) -> {
					Method method = methodMapping.get(type);
					if (method == null) {
						return null;
					}
					try {
						return method.invoke(resTemp, name);
					} catch (Exception e) {
						logger.error("从ResultSet取值失败，name = {}", name, e);
					} 
					return null;
				});
				list.add(bean);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close(statement, resultSet);
		}
		return list;
	}
	
	/**
	 * 释放Statement和ResultSet
	 */
	private void close(Statement statement, ResultSet resultSet) {
		try {
			if (resultSet != null) { 
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 将JavaBean数据转为参数数组，参数下标与sql中字段的下标对应
	 * @param sql SQL语句
	 * @param bean JavaBean数据
	 * @return 参数数组
	 */
	private Object[] convertBeanToParams(String sql, Object bean) {
		List<Object> params = new ArrayList<Object>();
		// 获取所有要插入的字段名
		List<String> columns = null;
		// 判断是插入sql还是修改sql
		if (sql.contains(KEY_WORD_INSERT) || sql.contains(KEY_WORD_INSERT.toUpperCase())) {
			columns = Arrays.asList(sql.substring(sql.indexOf("(") + 1, sql.indexOf(")")).split(","));
		} else {
			// UPDATE books SET name = ?, author = ?, price = ?, sales = ?, stock = ?, imgPath = ? WHERE id = ?
			String regex = "(\\w+)\\s*=\\s*\\?";
			columns = RegexUtils.extract(regex, sql, Pattern.CASE_INSENSITIVE);
		}
		logger.debug("从sql中提取字段，根据字段顺序将Bean转换为SQL参数数组。columns={}", columns);
		Class<?> clazz = bean.getClass();
		for (String column : columns) {
			column = column.trim();
			// 获取每个字段对应getter方法名
			String getterName = "get" + column.substring(0, 1).toUpperCase() + column.substring(1);
			try {
				// 获取getter方法
				Method method = clazz.getDeclaredMethod(getterName);
				if (method != null) {
					// 执行getter方法，获取数据
					params.add(method.invoke(bean));
				}
			} catch (Exception e) {
				logger.error("JavaBean数据转参数数组失败", e);
				params.add(null);
			}
		}
		return params.toArray();
	}
	
	/**
	 * 确定ResultSet中方法返回值与方法实例的映射关系
	 */
	private static void determineMethodMappingForResultSet() {
		Method[] methods = ResultSet.class.getDeclaredMethods();
		for (Method method : methods) {
			// 排除方法名不包含 get 关键字的方法 和 方法参数数量不为1的方法
			if (!method.getName().contains(METHOD_PREFIX) || method.getParameterCount() != PARAMETER_COUNT) {
				continue;
			}
			// 排除方法参数不是String类型的方法
			Parameter[] parameters = method.getParameters();
			if (parameters[0].getType() != String.class) {
				continue;
			}
			// 添加映射关系
			methodMapping.put(method.getReturnType(), method); 
		}
		methodMapping.put(Boolean.class, methodMapping.get(boolean.class));
		methodMapping.put(Integer.class, methodMapping.get(int.class));
		methodMapping.put(Date.class, methodMapping.get(Timestamp.class));
		logger.info("ResultSet.methodMapping初始化完毕");
		logger.debug("ResultSet.methodMapping: {}", methodMappingToString());
	}
	
	/**
	 * 将methodMapping格式化，便于日志打印
	 * @return 格式化后的字符串 
	 */
	private static String methodMappingToString() {
		StringBuffer buffer = new StringBuffer("[");
		for (Entry<Class<?>, Method> entry : methodMapping.entrySet()) {
			buffer.append("\n\t")
				.append(entry.getKey().getSimpleName())
				.append("\t: ")
				.append(entry.getValue().getName());
		}
		buffer.append("\n]");
		return buffer.toString();
	}
}












